  CREATE OR REPLACE
  procedure p_reset_sequences(p_verboseOnly NUMBER := 1)
  as
  
    TYPE seq_vs_table_type  IS RECORD
      ( table_name      user_constraints.table_name%TYPE,
        column_name     user_cons_columns.column_name%TYPE,       
        sequence_name   user_sequences.sequence_name%TYPE,
        min_value       user_sequences.min_value%TYPE,
        increment_by    user_sequences.increment_by%TYPE,
        cache_size      user_sequences.cache_size%TYPE,
        cycle_flag      user_sequences.cycle_flag%TYPE,
        seq_precision   varchar2(32));
        
    TYPE ref_cursor IS REF CURSOR RETURN seq_vs_table_type;
    
    c_sequence ref_cursor;
    
    TYPE t_sequence IS TABLE OF seq_vs_table_type
        INDEX BY PLS_INTEGER;
    
     l_sequence        t_sequence;
     
     l_row             PLS_INTEGER;
     v_sql             VARCHAR2(200);
     v_pkmaxvalue      NUMBER;
     v_ddl_drop        VARCHAR2(4000);
     v_ddl_create      VARCHAR2(4000);   
  
     v_cache_size      NUMBER := 20;
     v_increment_by    NUMBER;
     v_cycle           VARCHAR2(10);
    
  BEGIN
     DBMS_OUTPUT.PUT_LINE('Getting sequences');
        
     OPEN  c_sequence FOR 
     SELECT tab.table_name,
            tab.column_name,          
            seq.sequence_name,
            seq.min_value,
            seq.increment_by,
            seq.cache_size,
            seq.cycle_flag,
            lpad('9', nvl(dtc.data_precision,10), '9') as seq_precision
        FROM user_sequences seq, user_tab_cols dtc, aux_sequence_tab_col tab
        WHERE seq.sequence_name = tab.sequence_name
          AND dtc.table_name = tab.table_name
          AND dtc.column_name = tab.column_name
          AND dtc.data_type = 'NUMBER'
        ORDER BY seq.sequence_name;
  
    LOOP
      FETCH c_sequence BULK COLLECT INTO l_sequence LIMIT 1000;
      EXIT WHEN l_sequence.COUNT = 0;
      
      DBMS_OUTPUT.PUT_LINE('Count = ' || to_char(l_sequence.COUNT) );
      
      l_row := l_sequence.FIRST;
      WHILE l_row IS NOT NULL
      LOOP
  
        v_sql := 'select max(' || l_sequence(l_row).column_name || ') from ' || l_sequence(l_row).table_name;
        EXECUTE IMMEDIATE v_sql INTO v_pkmaxvalue;
  
        IF l_sequence(l_row).cache_size > 20
        THEN
          v_cache_size := l_sequence(l_row).cache_size;
        ELSE
          v_cache_size := 20;
        END IF;
  
        IF l_sequence(l_row).cycle_flag = 'Y'
        THEN
          v_cycle := 'CYCLE';
        ELSE 
          v_cycle := 'NOCYCLE';
        END IF;
        
        --- OBS.: this is to correct huge "increment by" (usualy a create sequence error)
        IF l_sequence(l_row).increment_by > 99 THEN
          v_increment_by := 1;
        ELSE
          v_increment_by := l_sequence(l_row).increment_by;
        END IF;
  
        IF v_pkmaxvalue IS NULL
        THEN
          v_pkmaxvalue := l_sequence(l_row).min_value;
        END IF;
  
  
        v_ddl_drop := 'DROP SEQUENCE ' || l_sequence(l_row).sequence_name;
  
        v_ddl_create:= 'CREATE SEQUENCE ' || l_sequence(l_row).sequence_name || 
                       ' MINVALUE ' || l_sequence(l_row).min_value ||
                       ' MAXVALUE ' || l_sequence(l_row).seq_precision ||
                       ' INCREMENT BY ' || v_increment_by ||
                       ' START WITH ' ||  v_pkmaxvalue ||
                       ' CACHE ' || v_cache_size || ' ' || v_cycle || ' NOORDER';

        IF p_verboseOnly = 1
        THEN
          DBMS_OUTPUT.PUT_LINE(v_ddl_drop || ';');
          DBMS_OUTPUT.PUT_LINE(v_ddl_create || ';');
        ELSE
          DBMS_OUTPUT.PUT('Reseting sequence ' || l_sequence(l_row).sequence_name || ' ( ');
          DBMS_OUTPUT.PUT(v_ddl_create || ' ) ... ');
          EXECUTE IMMEDIATE v_ddl_drop;
          EXECUTE IMMEDIATE v_ddl_create;
          DBMS_OUTPUT.PUT_LINE('DONE!');
        END IF;
        
        l_row := l_sequence.NEXT(l_row);
      END LOOP;
      
    END LOOP;
  
    IF c_sequence%ISOPEN
    THEN
      CLOSE c_sequence;
    END IF;
  
  EXCEPTION 
  WHEN OTHERS THEN
      IF c_sequence%ISOPEN
      THEN
        CLOSE c_sequence;
      END IF;
      RAISE;
  END p_reset_sequences;
  /